package avicit.bdp.dms.tdm.service.adapter;

import avicit.bdp.common.service.dto.DataSourceDTO;
import avicit.bdp.common.service.service.DataQueryService;
import avicit.bdp.common.service.service.DataSourceService;
import avicit.bdp.common.utils.CheckParaUtils;
import avicit.bdp.common.utils.database.adapter.BaseDBAdapter;
import avicit.bdp.common.utils.database.adapter.DbAdapterUtils;
import avicit.bdp.core.poi.util.csv.ExportCSVUtil;
import avicit.bdp.core.util.json.JSONUtils;
import avicit.bdp.dms.dss.dto.ColumnInfoDto;
import avicit.bdp.dms.tdm.dto.BdpAssetDTO;
import avicit.bdp.dms.tdm.dto.BdpAssetMountDataServiceDTO;
import avicit.bdp.dms.tdm.dto.ColumnDTO;
import avicit.bdp.dms.tdm.service.BdpAssetMountDataServiceService;
import avicit.bdp.dms.tdm.service.BdpAssetService;
import avicit.platform6.core.exception.BusinessException;
import com.alibaba.fastjson2.JSONObject;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class DataServiceAdapter {

    private static final Logger logger = LoggerFactory.getLogger(DataServiceAdapter.class);

    @Autowired
    private DataQueryService dataQueryService;
    @Autowired
    private BdpAssetMountDataServiceService assetMountDataService;
    @Autowired
    private DataSourceService dataSourceService;
    @Autowired
    private BdpAssetService bdpAssetService;

    public int downloadDataFromDBToCsv(String assetId, HttpServletResponse response) throws Exception {
        // TODO 下载数据量Integer.MAX_VALUE
        int pageSize = Integer.MAX_VALUE;

        BdpAssetDTO assetDTO = bdpAssetService.selectByPrimaryKey(assetId);

        Map<String, Object> map = getDataListFromDB(assetId, 1, pageSize, null);
        List<ColumnInfoDto> columnInfoDtoList = (List<ColumnInfoDto>) map.get("columnList");
        if (CollectionUtils.isEmpty(columnInfoDtoList)) {
            return 0;
        }

        String[] tableHeaderArr = new String[columnInfoDtoList.size()];
        int i = 0;
        for (ColumnInfoDto columnInfo : columnInfoDtoList) {
            tableHeaderArr[i] = columnInfo.getName();
            i++;
        }

        List<Object[]> cellList = new ArrayList<>();
        String fileName = assetDTO.getName() + ".csv";
        List<Map<String, Object>> resultList = (List<Map<String, Object>>) map.get("resultList");
        if (CollectionUtils.isNotEmpty(resultList)) {
            List<Object> mapValuesList;
            Object[] obj;

            for (Map<String, Object> dataMap : resultList) {
                mapValuesList = new ArrayList<>(dataMap.size());
                for (ColumnInfoDto columnInfo : columnInfoDtoList) {
                    mapValuesList.add(dataMap.get(columnInfo.getName()));
                }

                obj = mapValuesList.toArray();
                cellList.add(obj);
            }
        }

        byte[] bytes = ExportCSVUtil.writeCsvAfterToBytes(tableHeaderArr, cellList);
        ExportCSVUtil.downLoadCsv(fileName, bytes, response);
        return resultList.size();
    }

    /**
     * 从数据库实时查询数据
     *
     * @return 查询数据
     */
    public Map<String, Object> getDataListFromDB(String assetId, Integer currentPage, Integer pageSize, Map<String, Object> parameterMap) {
        BdpAssetMountDataServiceDTO assetMountData = assetMountDataService.queryByAssetId(assetId);
        CheckParaUtils.checkDbObjectValidity(assetMountData, logger, String.format("没有查到数据，请确保assetId:[%s]的挂载数据是否存在", assetId));

        return getDataListFromDB(assetMountData, currentPage, pageSize, parameterMap);
    }

    /**
     * 从数据库实时查询数据
     *
     * @return 查询数据
     */
    public Map<String, Object> getDataListFromDB(BdpAssetMountDataServiceDTO assetMountData, Integer currentPage, Integer pageSize, Map<String, Object> parameterMap) {
        Map<String, Object> map = new HashMap<>(8);

        List<ColumnDTO> columnInfoDtoList = JSONUtils.toList(assetMountData.getColumnInfo(), ColumnDTO.class);
        List<ColumnDTO> returnColumnList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(columnInfoDtoList)) {
            for (ColumnDTO columnInfo : columnInfoDtoList) {
                if (columnInfo.getIsReturn() != null && columnInfo.getIsReturn()) {
                    columnInfo.setName(columnInfo.getName().trim());
                    returnColumnList.add(columnInfo);
                }
            }
        }
        // 无字段直接返回
        if (CollectionUtils.isEmpty(returnColumnList)) {
            return map;
        }

        String tableName = assetMountData.getTableName();
        //支持sql方式资产
        if ("2".equalsIgnoreCase(assetMountData.getMountType())) {
            tableName = "(" + assetMountData.getSqlScript() + ") tmp";
        }
        String sql = buildSql(tableName, columnInfoDtoList, parameterMap);
        logger.info("sql = {}", sql);

        String dataSourceId = assetMountData.getDatasourceId();
        map = dataQueryService.getDataListByTable(null, dataSourceId, sql, parameterMap, currentPage, pageSize);
        map.put("assetMountData", assetMountData);
        map.put("columnList", returnColumnList);
        map.remove("modelInfo");
        return map;
    }

    /**
     * 组装查询sql语句
     *
     * @param tableName
     * @param columnInfoDtoList 模型列信息
     * @return 组装sql语句
     */
    private String buildSql(String tableName,
                            List<ColumnDTO> columnInfoDtoList,
                            Map<String, Object> parameterMap) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("select ");
        //处理返回列
        List<String> columns = new ArrayList<>();
        for (ColumnDTO columnInfo : columnInfoDtoList) {
            if (columnInfo.getIsReturn() != null && columnInfo.getIsReturn()) {
                columns.add(columnInfo.getName());
            }
        }
        sqlBuilder.append(String.join(",", columns));
        sqlBuilder.append(" from ").append(tableName);

        if (MapUtils.isNotEmpty(parameterMap)) {
            if (!sqlBuilder.toString().contains("where")) {
                sqlBuilder.append(" where 1 =1 ");
            }
            // 处理请求的参数
            for (ColumnDTO columnInfoDto : columnInfoDtoList) {
                if (columnInfoDto.getIsParam() != null && columnInfoDto.getIsParam() && parameterMap.containsKey(columnInfoDto.getName())) {
                    String isSymbol = columnInfoDto.getIsSymbol();
                    if (StringUtils.isBlank(isSymbol)) {
                        isSymbol = "=";
                    }
                    sqlBuilder.append(" and ")
                            .append(columnInfoDto.getName()).append(" ")
                            .append(isSymbol).append(" ")
                            .append(MapUtils.getString(parameterMap, columnInfoDto.getName()));
                }
            }
        }
        return sqlBuilder.toString();
    }

    public Map<String, Object> getDataListFromDBNew(String assetId, Integer pageNo, Integer pageSize, String paramJson) {
        Map<String, Object> map = new HashMap<>(8);
        BdpAssetMountDataServiceDTO assetMountData = assetMountDataService.queryByAssetId(assetId);
        CheckParaUtils.checkDbObjectValidity(assetMountData, logger, String.format("没有查到数据，请确保assetId:[%s]的挂载数据是否存在", assetId));

        List<ColumnDTO> columnInfoDtoList = JSONUtils.toList(assetMountData.getColumnInfo(), ColumnDTO.class);
        List<ColumnDTO> returnColumnList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(columnInfoDtoList)) {
            for (ColumnDTO columnInfo : columnInfoDtoList) {
                if (columnInfo.getIsReturn() != null && columnInfo.getIsReturn()) {
                    columnInfo.setName(columnInfo.getName().trim());
                    returnColumnList.add(columnInfo);
                }
            }
        }
        // 无字段直接返回
        if (CollectionUtils.isEmpty(returnColumnList)) {
            return map;
        }

        String tableName = assetMountData.getTableName();
        //支持sql方式资产
        if ("2".equalsIgnoreCase(assetMountData.getMountType())) {
            tableName = "(" + assetMountData.getSqlScript() + ") tmp";
        }
        String dataSourceId = assetMountData.getDatasourceId();
        String sql = buildSqlNew(tableName, returnColumnList, paramJson, dataSourceId);
        logger.info("sql = {}", sql);


        map = dataQueryService.getDataListByTable(null, dataSourceId, sql, null, pageNo, pageSize);
        map.put("assetMountData", assetMountData);
        map.put("columnList", returnColumnList);
        map.remove("modelInfo");
        return map;
    }

    private String buildSqlNew(String tableName,
                               List<ColumnDTO> columnInfoDtoList, String paramJson, String dataSourceId) {
        String sql = "";
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("select ");

        List<String> columns = new ArrayList<>();
        for (ColumnDTO columnInfo : columnInfoDtoList) {
            if (columnInfo.getIsReturn() != null && columnInfo.getIsReturn()) {
                columns.add(columnInfo.getName());
            }
        }
        sqlBuilder.append(String.join(",", columns));

        sqlBuilder.append(" from ").append(tableName);
        sql = sqlBuilder.toString();
        if (StringUtils.isNotBlank(paramJson)) {
            sql = handleSql(sql, paramJson, dataSourceId);
        }

        return sql;
    }

    private String handleSql(String sql, String paramJson, String dataSourceId) {
        //解析json
        Map<String, Object> jsonMap = JSONObject.parseObject(paramJson);
        List<Map<String, Object>> mapList = (List<Map<String, Object>>) jsonMap.get("param");
        //如果有条件，判断是否加where
        if (CollectionUtils.isNotEmpty(mapList)) {
            if (sql.contains("where")) {
                sql = sql + " and " + buildQueryParamSql(dataSourceId, paramJson);
            } else {
                sql = sql + " where " + buildQueryParamSql(dataSourceId, paramJson);
            }
        } else {
            sql = sql + buildQueryParamSql(dataSourceId, paramJson);
        }
        return sql;
    }

    private String buildQueryParamSql(String dataSourceId, String paramJson) {
        DataSourceDTO dataSourceDTO = dataSourceService.selectByPrimaryKey(dataSourceId);
        String datasourceType = dataSourceDTO.getDatasourceType();
        BaseDBAdapter dbAdapter = DbAdapterUtils.getDbAdapter(datasourceType);
        //解析json
        Map<String, Object> jsonMap = JSONObject.parseObject(paramJson);
        List<Map<String, Object>> mapList = (List<Map<String, Object>>) jsonMap.get("param");
        Map<String, Object> orderMap = (Map<String, Object>) jsonMap.get("sort");
        StringBuilder whereSql = new StringBuilder();
        if (CollectionUtils.isNotEmpty(mapList)) {
            int count = 0;
            for (Map<String, Object> stringObjectMap : mapList) {
                count++;
                String name = MapUtils.getString(stringObjectMap, "name", "");
                String condition = MapUtils.getString(stringObjectMap, "condition", "");
                String type = MapUtils.getString(stringObjectMap, "type");
                String value = MapUtils.getString(stringObjectMap, "value", "");
                if (StringUtils.isAnyBlank(name, condition, value)) {
                    name = StringUtils.isBlank(name) ? "?" : name;
                    condition = StringUtils.isBlank(condition) ? "?" : condition;
                    value = StringUtils.isBlank(value) ? "?" : value;
                    throw new BusinessException("请补全查询条件：" + name + " " + condition + " " + value);
                }
                Boolean flag = dbAdapter.getNumberType().contains(type.toLowerCase());
                whereSql.append(" " + name).append(" " + condition + " ");
                if ("in".equals(condition)) {
                    StringBuilder values = new StringBuilder();
                    values.append("(");
                    String[] vals = value.split(",");
                    int valcount = 0;
                    for (String val : vals) {
                        valcount++;
                        if (!flag) {
                            values.append("'");
                        }
                        values.append(val);
                        if (!flag) {
                            values.append("'");
                        }
                        if (valcount < vals.length) {
                            values.append(",");
                        }
                    }
                    values.append(")");
                    whereSql.append(values);
                } else if ("like".equals(condition)) {
                    whereSql.append("'%" + value + "%'");
                } else {
                    if ("oracle".equals(datasourceType.toLowerCase())) {
                        if ("date".equals(type.toLowerCase())) {
                            whereSql.append("to_date('" + value + "', 'yyyy-mm-dd hh24:mi:ss')");
                        } else if ("timestamp".equals(type.toLowerCase())) {
                            whereSql.append("to_timestamp('" + value + "', 'yyyy-mm-dd hh24:mi:ss')");
                        } else {
                            if (!flag) {
                                whereSql.append("'" + value + "'");
                            } else {
                                whereSql.append(value);
                            }
                        }
                    } else {
                        if (!flag) {
                            whereSql.append("'" + value + "'");
                        } else {
                            whereSql.append(value);
                        }
                    }
                }
                if (count < mapList.size()) {
                    whereSql.append(" and ");
                }
            }
        }
        if (orderMap != null) {
            String orderName = MapUtils.getString(orderMap, "name");
            if (StringUtils.isNotBlank(orderName)) {
                String orderType = MapUtils.getString(orderMap, "type");
                whereSql.append(" order by " + orderName + " " + orderType);
            }
        }
        return whereSql.toString();
    }
}
